package com.sxj.corejava.code02_jdbc;

import com.sxj.corejava.entity.ClassBean;
import com.sxj.corejava.entity.StudentBean;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 石小俊
 * @date 2023年12月2023/12/2日 14:03
 * 1.保存班级以及该班中的所有学生信息
 * 2.根据班级号查询指定班级以及该班级中所有学生信息
 * 3.查询所有学生以及其所属班级信息
 */
public class Test08_学生管理系统 {

    public static void main(String[] args) {
//        ClassBean classBean = new ClassBean("wbs22002","计算机科学与技术");
//        List<StudentBean> students = new ArrayList<>();
//        StudentBean studentBean = new StudentBean("2022000101","admin",20);
//        StudentBean studentBean2 = new StudentBean("2022000102","jack",21);
//        StudentBean studentBean3 = new StudentBean("2022000103","alice",19);
//        students.add(studentBean);
//        students.add(studentBean2);
//        students.add(studentBean3);
//        insert(classBean,students);

//        List<StudentBean> students = selectAll();
//        for(StudentBean student : students){
//            System.out.println(student);
//        }

        ClassBean classBean = selectByNo("wbs22002");
        System.out.println(classBean);
    }


    public static void insert(ClassBean classBean, List<StudentBean> students) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Integer id = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jdbc?useUnicode=true&characterEncoding=utf-8", "root", "");
            String sql = new StringBuffer()
                    .append(" insert into ")
                    .append(" t_class ")
                    .append("   (no,major)")
                    .append(" values ")
                    .append("   (?,?)")
                    .toString();
            ps = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
            ps.setString(1, classBean.getNo());
            ps.setString(2, classBean.getMajor());
            ps.executeUpdate();
            rs = ps.getGeneratedKeys();
            while (rs.next()) {
                id = rs.getInt(1);
            }
            classBean.setId(id);
            sql = new StringBuffer()
                    .append(" insert into ")
                    .append(" t_student ")
                    .append("   (no,name,age,class_id)")
                    .append(" values ")
                    .append("   (?,?,?,?)")
                    .toString();
            ps = conn.prepareStatement(sql);
            for (StudentBean studentBean : students) {
                // 每个学生都属于当前保存的班级
                studentBean.setClassBean(classBean);
                ps.setString(1, studentBean.getNo());
                ps.setString(2, studentBean.getName());
                ps.setInt(3, studentBean.getAge());
                ps.setInt(4, studentBean.getClassBean().getId());
                // 每遍历一次,保存一个学生信息
                ps.executeUpdate();
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("未找到驱动");
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("连接数据库失败");
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    System.out.println("释放连接出错");
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static ClassBean selectByNo(String no) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ClassBean classBean = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jdbc?useUnicode=true&characterEncoding=utf-8", "root", "");
            String sql = new StringBuffer()
                    .append(" select c.id,c.no,c.major,s.id 's.id',s.no 's.no',s.name,s.age ")
                    .append(" from t_class c ")
                    .append(" left join t_student s ")
                    .append("   on c.id = s.class_id ")
                    .append(" where c.no = ? ")
                    .toString();
            ps = conn.prepareStatement(sql);
            ps.setString(1, no);
            rs = ps.executeQuery();
            while (rs.next()) {
                if (classBean == null) {
                    classBean = new ClassBean();
                    classBean.setId(rs.getInt("id"));
                    classBean.setNo(rs.getString("no"));
                    classBean.setMajor(rs.getString("major"));
                }

                StudentBean student = new StudentBean();
                student.setId(rs.getInt("s.id"));
                student.setNo(rs.getString("s.no"));
                student.setName(rs.getString("name"));
                student.setAge(rs.getInt("age"));
                classBean.addStudent(student);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("未找到驱动");
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("连接数据库失败");
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    System.out.println("释放连接出错");
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return classBean;
    }

    public static List<StudentBean> selectAll() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<StudentBean> students = new ArrayList<>();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jdbc?useUnicode=true&characterEncoding=utf-8", "root", "");
            String sql = new StringBuffer()
                    .append(" select s.id,s.no,s.name,s.age,c.id 'c.id',c.no 'c.no',c.major ")
                    .append(" from t_student s ")
                    .append(" left join t_class c ")
                    .append("   on s.class_id = c.id ")
                    .toString();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                StudentBean student = new StudentBean();
                student.setId(rs.getInt("id"));
                student.setNo(rs.getString("no"));
                student.setName(rs.getString("name"));
                student.setAge(rs.getInt("age"));

                ClassBean classBean = new ClassBean();
                classBean.setId(rs.getInt("c.id"));
                classBean.setNo(rs.getString("c.no"));
                classBean.setMajor(rs.getString("major"));

                student.setClassBean(classBean);
                students.add(student);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("未找到驱动");
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("连接数据库失败");
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    System.out.println("释放连接出错");
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return students;
    }
}
